package salaryDao;

import JDBCtils.DBUtiles;
import salaryEntity.emSalaryEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class employeeSalaryDao {
    private static emSalaryEntity getEmSalaryEntityDao(ResultSet resultSet)throws SQLException {
        String employeename = resultSet.getString(1);
        String employeeID = resultSet.getString(2);
        String employeeSalary = resultSet.getString(3);
        String employeeExtraSalary = resultSet.getString(4);
        String employeeTotalSalary = resultSet.getString(5);

        emSalaryEntity salary=new emSalaryEntity();
        salary.setEmployeename(employeename);
        salary.setEmployeeID(employeeID);
        salary.setEmployeeSalary(employeeSalary);
        salary.setEmployeeExtraSalary(employeeExtraSalary);
        salary.setEmployeeTotalSalary(employeeTotalSalary);
        return salary;
    }
//    ----------------查询Salary--------------------------------
    public static List<emSalaryEntity> getEmSalaryDao(int page, int limit){
        List<emSalaryEntity> em=new ArrayList<>();
        Connection connection = DBUtiles.getConnect();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareCall("select * from salary limit ?, ?");
            statement.setInt(1, (page - 1) * limit);
            statement.setInt(2, limit);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next())
            {
                emSalaryEntity st=getEmSalaryEntityDao(resultSet);

                em.add(st);
            }
            DBUtiles.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();

        }
        return em;
    }
//    -----------------------查询总的条数-----------------------------
    public static int  totalCount(){
        Connection connection=DBUtiles.getConnect();
        String sql="select count(*) from salary";
        int rows=0;
        try {
            PreparedStatement statement=connection.prepareCall(sql);
            ResultSet resultSet=statement.executeQuery();
            if (resultSet.next()){
                rows=resultSet.getInt(1);
            }
            DBUtiles.close(resultSet,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }
    //    ------------------------获得单行查询结果----------------------------------
    public static emSalaryEntity getEmployeeSalaryById(String employeeID)
    {
        Connection connection = DBUtiles.getConnect();
        String sql = "select * from salary where employeeID =?" ;
        PreparedStatement statement;
        emSalaryEntity employee = null;
        try {
            statement = connection.prepareCall(sql);
            statement.setString(1, employeeID);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
            {
                employee =getEmSalaryEntityDao(resultSet);
            }
            DBUtiles.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return employee;
    }

    //    -----------------------------删除操作---------------------------------
    public static int delEmSalaryEmployeeById(String employeeID){
        Connection connection=DBUtiles.getConnect();
        String sql="delete from salary where employeeID=?";
        int rows=0;
        try {
            PreparedStatement statement= connection.prepareCall(sql);
            statement.setString(1,employeeID);
            rows=statement.executeUpdate();
            DBUtiles.close(null,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }
    //----------------------------更新操作----------------------------------------
    public static int updateEmployeeSalary(emSalaryEntity employee){
        Connection connection = DBUtiles.getConnect();
        String sql = "update salary set employeename= ?,employeeSalary=?,employeeExtraSalary=?,employeeTotalSalary=? where employeeID= ?";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            statement.setString(1,employee.getEmployeename());
            statement.setString(2, employee.getEmployeeSalary());
            statement.setString(3, employee.getEmployeeExtraSalary());
            statement.setString(4, employee.getEmployeeTotalSalary());
            statement.setString(5, employee.getEmployeeID());
            rows = statement.executeUpdate();
            DBUtiles.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }
    //-------------------------添加数据---------------------------------------
    public static int addSalaryEmTable(emSalaryEntity employee){
        Connection connection=DBUtiles.getConnect();
        String sql = "insert into salary(employeename,employeeID,employeeSalary,employeeExtraSalary,employeeTotalSalary) values (?,?,?,?,?)";
        int rows=0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            statement.setString(1,employee.getEmployeename());
            statement.setString(2,employee.getEmployeeID() );
            statement.setString(3,employee.getEmployeeSalary());
            statement.setString(4,employee.getEmployeeExtraSalary());
            statement.setString(5,employee.getEmployeeTotalSalary() );
            rows= statement.executeUpdate();
            DBUtiles.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }
}
